/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sampleBean;

import java.util.Date;
import java.util.List;
import javax.ejb.Stateful;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.jboss.ejb3.annotation.RemoteBinding;
import sampleEntity.Account;
import sampleEntity.Bill;
import sampleEntity.BillDetail;
import sampleEntity.BillDetailPK;
import sampleEntity.BookRoom;
import sampleEntity.BookRoomPK;
import sampleEntity.Customers;
import sampleEntity.Product;
import sampleEntity.Room;

/**
 *
 * @author ASUS K43E
 */
@Stateful
@RemoteBinding(jndiBinding = "remoteJNDI")
public class CusSessionBean implements CusSessionBeanRemote {

    @PersistenceContext(unitName = "lafitte-ejbPU")
    private EntityManager em;

    public void persist(Object object) {
        em.persist(object);
    }

    public boolean checkLogin(String username, String password) {
        String jpql = "SELECT a FROM Account a WHERE a.username = :username "
                + "and a.password = :password and a.arole <> :arole";
        Query query = em.createQuery(jpql);
        query.setParameter("username", username);
        query.setParameter("password", password);
        query.setParameter("arole", 4);
        List list = query.getResultList();
        if (list.size() > 0) {
            return true;
        }
        return false;
    }

    public int checkRole(String username) {
        Account acc = em.find(Account.class, username);
        int role = acc.getArole();
        return role;
    }

    public int checkRank(String username) {
        Customers reg = em.find(Customers.class, username);
        int rank = 0;
        if (reg.getRank().equals("Silver")) {
            rank = 1;
        } else if (reg.getRank().equals("Gold")) {
            rank = 2;
        }
        return rank;
    }

    public List showRoom(Date date) {
        Query query = em.createNamedQuery("BookRoom.findByOrderDate");
        query.setParameter("orderDate", date);
        List list = query.getResultList();
        return list;
    }

    public List showRoomList() {
        Query query = em.createNamedQuery("Room.findByRoomType");
        query.setParameter("roomType", false);
        List list = query.getResultList();
        return list;
    }

    public boolean addNewBook(String roomId, Date date, String username) {
        String jpql = "SELECT b FROM BookRoom b WHERE b.bookRoomPK.roomId = :roomId and b.bookRoomPK.orderDate = :orderDate";
        Query query = em.createQuery(jpql);
        query.setParameter("roomId", roomId);
        query.setParameter("orderDate", date);
        List list = query.getResultList();
        if (list.isEmpty()) {
            Room r = em.find(Room.class, roomId);
            Customers cus = em.find(Customers.class, username);
            BookRoom newObj = new BookRoom(new BookRoomPK(roomId, date), false, r, cus);
            persist(newObj);
            return true;
        }
        return false;
    }

    public List findGoldRoom() {
        Query query = em.createNamedQuery("Room.findByRoomType");
        query.setParameter("roomType", true);
        List list = query.getResultList();
        return list;
    }

    public List findBookRoom(String username, Date date) {
        Customers cus = em.find(Customers.class, username);
        String jpql = "SELECT b FROM BookRoom b WHERE b.bookRoomPK.orderDate = :orderDate and b.customers = :customers";
        Query query = em.createQuery(jpql);
        query.setParameter("orderDate", date);
        query.setParameter("customers", cus);
        List list = query.getResultList();
        return list;
    }

    public boolean cancelBooking(String roomId, Date date) {
        String jpql = "SELECT b FROM BookRoom b WHERE b.bookRoomPK.roomId = :roomId and b.bookRoomPK.orderDate = :orderDate";
        Query query = em.createQuery(jpql);
        query.setParameter("roomId", roomId);
        query.setParameter("orderDate", date);
        List list = query.getResultList();
        if (list.size() > 0) {
            BookRoom br = (BookRoom) list.get(0);
            em.remove(br);
            return true;
        }
        return false;
    }

    public List searchCus(String name) {
        String jpql = "SELECT a FROM Account a WHERE a.fullname LIKE :fullname and a.arole = :arole";
        Query query = em.createQuery(jpql);
        query.setParameter("fullname", "%" + name + "%");
        query.setParameter("arole", 3);
        List list = query.getResultList();
        return list;
    }

    public boolean blockAndLost(String username, boolean block, boolean lost) {
        Customers cus = em.find(Customers.class, username);
        if (cus != null) {
            cus.setIsBlock(block);
            cus.setIsLost(lost);
            persist(cus);

            String jpql = "SELECT b FROM BookRoom b WHERE b.status = :status and b.customers = :customers";
            Query query = em.createQuery(jpql);
            query.setParameter("status", false);
            query.setParameter("customers", cus);
            List list = query.getResultList();
            if (list.size() > 0) {
               BookRoom[] br=new BookRoom[list.size()];
               list.toArray(br);
               for(int i=0;i<br.length;i++){
                   em.remove(br[i]);
               }
            }
            return true;
        }
        return false;
    }

    public List checkNew() {
        Query query = em.createNamedQuery("Account.findByArole");
        query.setParameter("arole", 4);
        List list = query.getResultList();
        return list;
    }

    public boolean updateCard(String username, String cardId, int point, String rank, boolean block, boolean lost) {
        Customers cus = em.find(Customers.class, username);
        if (cus != null) {
            cus.setCardId(cardId);
            cus.setPoint(point);
            cus.setRank(rank);
            cus.setIsBlock(block);
            cus.setIsLost(lost);
            persist(cus);
            return true;
        }
        return false;
    }

    public List findBlock() {
        Query query = em.createNamedQuery("Customers.findByIsBlock");
        query.setParameter("isBlock", true);
        List list = query.getResultList();
        return list;
    }

    public List findLost() {
        Query query = em.createNamedQuery("Customers.findByIsLost");
        query.setParameter("isLost", true);
        List list = query.getResultList();
        return list;
    }

    public List findbyRank(String rank) {
        String jpql = "SELECT c FROM Customers c WHERE c.rank = :rank and c.username IN (SELECT a.username FROM Account a WHERE a.arole <> :arole)";
        Query query = em.createQuery(jpql);
        query.setParameter("rank", rank);
        query.setParameter("arole", 4);
        List list = query.getResultList();
        return list;
    }

    public boolean approve(String username) {
        Account acc = em.find(Account.class, username);
        int role = 3;
        if (acc != null) {
            acc.setArole(role);
            persist(acc);
            return true;
        }
        return false;
    }

    public boolean disapprove(String username) {
        Account acc = em.find(Account.class, username);
        Customers cus = em.find(Customers.class, username);
        if (acc != null) {
            em.remove(acc);
            em.remove(cus);
            return true;
        }
        return false;
    }

    public boolean checkRoom(String room, Date date) {
        String jpql = "SELECT b FROM BookRoom b WHERE b.bookRoomPK.roomId = :roomId and b.bookRoomPK.orderDate = :orderDate";
        Query query = em.createQuery(jpql);
        query.setParameter("roomId", room);
        query.setParameter("orderDate", date);
        List list = query.getResultList();
        if (list.size() > 0) {
            BookRoom[] br = new BookRoom[list.size()];
            list.toArray(br);
            br[0].setStatus(true);
            persist(br[0]);
            return true;
        }
        return false;
    }

    public List findDob() {
        String jpql = "SELECT c FROM Customers c WHERE DAY(c.dateOfBirth) = DAY(getDate()) and MONTH(c.dateOfBirth) = MONTH(getDate())";
        Query query = em.createQuery(jpql);
        List list = query.getResultList();
        return list;
    }

    public List productId() {
        Query query = em.createNamedQuery("Product.findAll");
        List list = query.getResultList();
        return list;
    }

    public List listRoom() {
        Query query = em.createNamedQuery("Room.findAll");
        List list = query.getResultList();
        return list;
    }

    public boolean addProduct(String username, Date date, String productId, int quantity) {
        Product pro = em.find(Product.class, productId);
        Customers cus = em.find(Customers.class, username);
        String jpql = "SELECT b FROM Bill b WHERE b.bDate = :date and b.customers = :customers";
        Query query = em.createQuery(jpql);
        query.setParameter("date", date);
        query.setParameter("customers", cus);
        List list = query.getResultList();
        if (list.isEmpty()) {
            //new Bill
            double payment = pro.getPrice() * quantity;
            int point = 0;
            if (payment >= 500 && payment < 1000) {
                point = 100;
            } else if (payment >= 1000 && payment < 2000) {
                point = 200;
            } else if (payment >= 2000 && payment < 5000) {
                point = 600;
            } else if (payment >= 5000 && payment < 10000) {
                point = 1000;
            } else if (payment >= 10000 && payment < 100000) {
                point = 5000;
            }
            Bill newBill = new Bill(0, payment, date, point, cus);
            persist(newBill);

            //new bill item
            list = query.getResultList();
            Bill[] bill = new Bill[list.size()];
            list.toArray(bill);
            BillDetail newItem = new BillDetail(new BillDetailPK(bill[0].getBillId(), productId), quantity, pro.getPrice());
            persist(newItem);
            return true;
        } else if (list.size() > 0) {
            Bill[] bill = new Bill[list.size()];
            list.toArray(bill);

            //kiem tra co chua
            String jpql1 = "SELECT b FROM BillDetail b WHERE b.billDetailPK.billId = :billId and b.billDetailPK.productId = :productId";
            Query query1 = em.createQuery(jpql1);
            query1.setParameter("billId", bill[0].getBillId());
            query1.setParameter("productId", productId);
            List item = query1.getResultList();

            //co
            if (item.size() > 0) {
                BillDetail[] items = new BillDetail[item.size()];
                item.toArray(items);
                int quan = items[0].getQuantity() + quantity;
                items[0].setQuantity(quan);
                persist(items[0]);
            } //chua co
            else {
                BillDetail newItem = new BillDetail(new BillDetailPK(bill[0].getBillId(), productId), quantity, pro.getPrice());
                persist(newItem);
            }

            double payment = bill[0].getPayment() + pro.getPrice() * quantity;
            int point = 0;
            if (payment >= 500 && payment < 1000) {
                point = 100;
            } else if (payment >= 1000 && payment < 2000) {
                point = 200;
            } else if (payment >= 2000 && payment < 5000) {
                point = 600;
            } else if (payment >= 5000 && payment < 10000) {
                point = 1000;
            } else if (payment >= 10000 && payment < 100000) {
                point = 5000;
            }
            Bill reg = em.find(Bill.class, bill[0].getBillId());
            reg.setPayment(payment);
            reg.setPoint(point);
            persist(reg);
            return true;
        }
        return false;
    }

    public List findBillItem(String username, Date date) {
        Customers cus = em.find(Customers.class, username);
        String jpql = "SELECT b FROM Bill b WHERE b.bDate = :date and b.customers = :customers";
        Query query = em.createQuery(jpql);
        query.setParameter("date", date);
        query.setParameter("customers", cus);
        List list = query.getResultList();
        if (list.size() > 0) {
            Bill[] bill = new Bill[list.size()];
            list.toArray(bill);
            Query query2 = em.createNamedQuery("BillDetail.findByBillId");
            int billId = bill[0].getBillId();
            query2.setParameter("billId", billId);
            list = query2.getResultList();
        }
        return list;
    }

    public Customers findCus(String username) {
        Customers cus = em.find(Customers.class, username);
        return cus;
    }

    public Bill findBill(String username, Date date) {
        Customers cus = em.find(Customers.class, username);
        String jpql = "SELECT b FROM Bill b WHERE b.bDate = :date and b.customers = :customers";
        Query query = em.createQuery(jpql);
        query.setParameter("date", date);
        query.setParameter("customers", cus);
        List list = query.getResultList();
        if (list.size() > 0) {
            Bill[] bills = new Bill[list.size()];
            list.toArray(bills);
            return bills[0];
        }
        return null;
    }

    public boolean addRoom(String username, String roomId, Date date) {
        Room r = em.find(Room.class, roomId);
        Customers cus = em.find(Customers.class, username);
        String jpql = "SELECT b FROM Bill b WHERE b.bDate = :date and b.customers = :customers";
        Query query = em.createQuery(jpql);
        query.setParameter("date", date);
        query.setParameter("customers", cus);
        List list = query.getResultList();
        if (list.isEmpty()) {
            double payment = r.getPrice();
            int point = 0;
            if (payment >= 500 && payment < 1000) {
                point = 100;
            } else if (payment >= 1000 && payment < 2000) {
                point = 200;
            } else if (payment >= 2000 && payment < 5000) {
                point = 600;
            } else if (payment >= 5000 && payment < 10000) {
                point = 1000;
            } else if (payment >= 10000 && payment < 100000) {
                point = 5000;
            }
            Bill newBill = new Bill(payment, payment, date, point, r, cus);
            persist(newBill);
            return true;
        } else if (list.size() > 0) {
            Bill[] bill = new Bill[list.size()];
            list.toArray(bill);

            double payment = r.getPrice() + bill[0].getPayment();
            int point = 0;
            if (payment >= 500 && payment < 1000) {
                point = 100;
            } else if (payment >= 1000 && payment < 2000) {
                point = 200;
            } else if (payment >= 2000 && payment < 5000) {
                point = 600;
            } else if (payment >= 5000 && payment < 10000) {
                point = 1000;
            } else if (payment >= 10000 && payment < 100000) {
                point = 5000;
            }
            bill[0].setRoom(r);
            bill[0].setRoomPrice(r.getPrice());
            bill[0].setPayment(payment);
            bill[0].setPoint(point);
            persist(bill[0]);
            return true;
        }
        return false;
    }

    public boolean printBill(String username, int point, String rank) {
        Customers cus = em.find(Customers.class, username);
        if (cus != null) {
            cus.setPoint(point);
            cus.setRank(rank);
            persist(cus);
            return true;
        }
        return false;
    }

    public boolean deletePro(String username, Date date, String productId) {
        Customers cus = em.find(Customers.class, username);
        String jpql = "SELECT b FROM Bill b WHERE b.bDate = :date and b.customers = :customers";
        Query query = em.createQuery(jpql);
        query.setParameter("date", date);
        query.setParameter("customers", cus);
        List list = query.getResultList();
        //neu co bill
        if (list.size() > 0) {
            Bill[] bills = new Bill[list.size()];
            list.toArray(bills);

            String jpql2 = "SELECT b FROM BillDetail b WHERE b.billDetailPK.billId = :billId and b.billDetailPK.productId = :productId";
            Query query2 = em.createQuery(jpql2);
            query2.setParameter("billId", bills[0].getBillId());
            query2.setParameter("productId", productId);
            List list2 = query2.getResultList();
            //neu co product
            if (list2.size() > 0) {
                BillDetail[] items = new BillDetail[list2.size()];
                list2.toArray(items);

                double payment = bills[0].getPayment() - items[0].getUnitPrice() * items[0].getQuantity();
                int point = 0;
                if (payment >= 500 && payment < 1000) {
                    point = 100;
                } else if (payment >= 1000 && payment < 2000) {
                    point = 200;
                } else if (payment >= 2000 && payment < 5000) {
                    point = 600;
                } else if (payment >= 5000 && payment < 10000) {
                    point = 1000;
                } else if (payment >= 10000 && payment < 100000) {
                    point = 5000;
                }

                bills[0].setPayment(payment);
                bills[0].setPoint(point);
                persist(bills[0]);
                em.remove(items[0]);
                if (payment == 0) {
                    em.remove(bills[0]);
                }
                return true;
            }
        }
        return false;
    }

    public boolean checkBlock(String username) {
        Customers cus = em.find(Customers.class, username);
        return cus.getIsBlock();
    }

    public boolean editInfo(String username, String job, String add, String phone, String bankAcc, String bankName) {
        Customers reg = em.find(Customers.class, username);
        Account acc = em.find(Account.class, username);

        if (reg != null) {
            reg.setJob(job);
            reg.setBankAccount(bankAcc);
            reg.setBankName(bankName);
            persist(reg);
            if (acc != null) {
                acc.setAddress(add);
                acc.setTelephone(phone);
                persist(acc);
                return true;
            }
        }
        return false;
    }

    public boolean changePass(String username, String newPass) {
        Account acc = em.find(Account.class, username);
        if (acc != null) {
            acc.setPassword(newPass);
            persist(acc);
            return true;
        }
        return false;
    }

    public List viewHistory(String username) {
        Customers cus = em.find(Customers.class, username);
        String jpql = "SELECT b FROM Bill b WHERE b.customers = :customers";

        Query query = em.createQuery(jpql);
        query.setParameter("customers", cus);

        List list = query.getResultList();
        return list;
    }

    public boolean register(String username, String password, String fullname, String address, String phone, String email) {
        Account newAcc = new Account(username, password, 4, fullname, email);
        persist(newAcc);
        return true;
    }

    public boolean checkUser(String username) {
        Account acc = em.find(Account.class, username);
        if (acc != null) {
            return true;
        }
        return false;
    }

    public boolean checkID(String id) {
        Query query = em.createNamedQuery("Customers.findById");
        query.setParameter("id", id);
        List acc = query.getResultList();
        if (acc.size() > 0) {
            return true;
        }
        return false;
    }

    public boolean checkEmail(String email) {
        Query query = em.createNamedQuery("Account.findByEmail");
        query.setParameter("email", email);
        List acc = query.getResultList();
        if (acc.size() > 0) {
            return true;
        }
        return false;
    }

    public boolean checkPass(String username, String password) {
        Account acc = em.find(Account.class, username);
        if (acc.getPassword().equals(password)) {
            return true;
        }
        return false;
    }

    public boolean addCus(String username, String id, Date dateOfBirth, String job, String bankAcc, String bankName, String gender) {
       // Account acc=em.find(Account.class, username);
        Customers customers=new Customers(username, id, dateOfBirth, gender, bankAcc, bankName, 0, bankAcc, true, false);
        persist(customers);
        return false;
    }
    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method")
}
